﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports WSFactory

Public Class AlmacenesDAL
    Public Function registraAlmacen(ByVal pAlmacen As AlmacenesBE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 4) As OdbcParameter

        Try

            instrSql = "INSERT INTO ""Almacenes""(""IdEmpresa"", ""IdAlmacen"", ""Id"", ""DesAlmacen"", ""Status"") " & _
                                        "VALUES (?,?,?,?,?);"

            oInsParm(0) = New OdbcParameter("", 1) 'pAlmacen.IdEmpresa)
            oInsParm(1) = New OdbcParameter("", 5) 'pAlmacen.IdAlmacen)
            oInsParm(2) = New OdbcParameter("", 5) 'pAlmacen.Id)
            oInsParm(3) = New OdbcParameter("", "ALMACEN5") 'pAlmacen.DesAlmacen)
            oInsParm(4) = New OdbcParameter("", "A") 'pAlmacen.Status)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function modificaAlmacen(ByVal pAlmacen As AlmacenesBE) As Boolean
        Dim instrSql As String
        Dim oUpdParm(0 To 4) As OdbcParameter
        Try
            instrSql = "UPDATE ""Almacenes"" " & _
                                        "SET ""DesAlmacen"" = ?, " & _
                                                """IdAlmacen"" = ?, " & _
                                                """Status"" = ? " & _
                                                "WHERE ""Id"" = ? " & _
                                            "AND ""IdEmpresa"" = ?;"

            oUpdParm(0) = New OdbcParameter("", "ALMACEN2") 'pAlmacen.DesAlmacen)
            oUpdParm(1) = New OdbcParameter("", 2) 'pAlmacen.IdAlmacen)
            oUpdParm(2) = New OdbcParameter("", "A") 'pAlmacen.Status)
            oUpdParm(3) = New OdbcParameter("", 2) 'pAlmacen.Id)
            oUpdParm(4) = New OdbcParameter("", 2) 'pAlmacen.IdEmpresa)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oUpdParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    Return True
                End Using
            End Using
            
        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oUpdParm = Nothing
        End Try
    End Function

    Public Function getAlmacenes(ByVal pIdEmpr As Integer, ByVal pStrAlmacen As String) As List(Of AlmacenesBE)
        Dim almLs As List(Of AlmacenesBE) = New List(Of AlmacenesBE)
        Dim instrSql As String

        Try
            instrSql = "SELECT ""IdEmpresa"", ""IdAlmacen"", ""Id"", ""DesAlmacen"", ""Status"" " & _
                                     "FROM ""Almacenes"" " & _
                                    "WHERE ""IdEmpresa"" = ? " & _
                                        "AND ""DesAlmacen"" LIKE '%" & pStrAlmacen & "%' ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim almacenes As AlmacenesBE = almacenesFact.Build(myDR)
                            If (Not almacenes Is Nothing) Then
                                almLs.Add(almacenes)
                            End If
                            almacenes = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return almLs
        Catch ex As Exception
            Throw
        Finally
            almLs = Nothing
            instrSql = Nothing
        End Try
    End Function

    Public Function getAlmacenesByEmpr(ByVal pIdEmpr As Integer) As List(Of AlmacenesBE)
        Dim almLs As List(Of AlmacenesBE) = New List(Of AlmacenesBE)
        Dim instrSql As String
        Try
            instrSql = "SELECT ""IdEmpresa"", ""IdAlmacen"", ""Id"", ""DesAlmacen"", ""Status"" " & _
                                     "FROM ""Almacenes"" " & _
                                    "WHERE ""IdEmpresa"" = ?;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim almacen As AlmacenesBE = almacenesFact.Build(myDR)
                            If (Not almacen Is Nothing) Then
                                almLs.Add(almacen)
                            End If
                            almacen = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return almLs
        Catch ex As Exception
            Throw
        Finally
            almLs = Nothing
            instrSql = Nothing
        End Try
    End Function

    Public Function getAlmacenById(ByVal pId As Integer, ByVal pIdEmpr As Integer) As AlmacenesBE
        Dim tmpAlm As AlmacenesBE
        Dim instrSql As String
        Try
            instrSql = "SELECT ""IdEmpresa"", ""IdAlmacen"", ""Id"", ""DesAlmacen"", ""Status"" " & _
                                     "FROM ""Almacenes"" " & _
                                    "WHERE ""Id"" = ? " & _
                                        "AND ""IdEmpresa"" = ?;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pId))
                    myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpAlm = almacenesFact.Build(myDR)
                        Else
                            tmpAlm = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpAlm
        Catch ex As Exception
            Throw
        Finally
            tmpAlm = Nothing
            instrSql = ""
        End Try
    End Function

    Public Function existeAlmacen(ByVal pAlmacen As String, ByVal pIdEmpr As Integer) As Boolean
        Dim existe As Boolean = False
        Dim instrSql As String
        Dim oSelParm(0 To 1) As OdbcParameter
        Try

            instrSql = "SELECT COUNT(*) " & _
                                    "FROM ""Almacenes"" " & _
                                    "WHERE ""IdEmpresa"" = ? " & _
                                        "AND ""DesAlmacen"" = ?;"

            oSelParm(0) = New OdbcParameter("", pIdEmpr)
            oSelParm(1) = New OdbcParameter("", pAlmacen)
            Dim count As Object = db.ExecScalar(instrSql, CommandType.Text, oSelParm, My.Settings.cs)
            If (Not count Is Nothing) Then
                If (CShort(count) > 0) Then
                    existe = True
                Else
                    existe = False
                End If
            Else
                existe = False
            End If
            Return existe
        Catch ex As Exception
            Throw
        Finally
            oSelParm = Nothing
            instrSql = Nothing
            existe = Nothing
        End Try
    End Function
End Class
